﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop;
using System.Diagnostics;
using log4net;
using log4net.Config;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using System.Configuration;

namespace ExcelReportGeneratorFormApp
{
    class ExcelApp
    {
        private bool saveExcel = false;
        private bool savePdf = false;

        // Create a logger
        ILog log = LogManager.GetLogger(typeof(Program));

        //Get the configuration file
        Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);

        private string TEMPLATE_FILE_EXCEL;
        private string REPORT_DIR_EXCEL;
        private string REPORT_DIR_PDF;
        private Int16 LIMIT = -1;

        private bool paramOpenAfterPublish = false;
        private bool paramIncludeDocProps = false;
        private bool paramIgnorePrintAreas = false;

        private object paramFromPage = Type.Missing;
        private object paramToPage = Type.Missing;

        // Excel app
        Excel.Application excelAppTemplate;

        // Workbook
        Excel.Workbook wbTemplate;

        public ExcelApp(bool saveExcel, bool savePdf)
        {
            this.saveExcel = saveExcel;
            this.savePdf = savePdf;
            this.TEMPLATE_FILE_EXCEL = @config.AppSettings.Settings["TemplateExcel"].Value;
            this.REPORT_DIR_EXCEL = @config.AppSettings.Settings["OutputPathExcel"].Value;
            this.REPORT_DIR_PDF = @config.AppSettings.Settings["OutputPathExcel"].Value;
            this.LIMIT = convertToInt(config.AppSettings.Settings["Limit"].Value);

            this.REPORT_DIR_EXCEL = appendSlash(this.REPORT_DIR_EXCEL);
            this.REPORT_DIR_PDF = appendSlash(this.REPORT_DIR_PDF);
        }

        private string appendSlash(string p)
        {
            if (!p.Substring(p.Length - 1, 1).Equals("\\")) 
            {
                return p + "\\";
            }
            return p;
        }

        private Int16 convertToInt(string s)
        {
            if (s != null && s.Trim().Length > 0)
            {
                return Int16.Parse(s);
            }

            return -1;
        }

        public void Run()
        {
            log.Debug("Open Excel Template");

            OpenExcelTemplate();

            try
            {
                Worksheet wsResponses = getWorksheetByName("responses");

                Excel.Range oRangeTarget = wsResponses.get_Range("A2");
                Excel.Range oRowTarget = oRangeTarget.EntireRow;

                int teller = 3;
                int max = 1000;
                if (LIMIT > 0)
                    max = LIMIT + 3;

                while (teller < max)
                {
                    Excel.Range oRangeSource = wsResponses.get_Range("A" + teller.ToString());
                    Excel.Range oRowSource = oRangeSource.EntireRow;
                    var oNaam = oRangeSource.get_Value(Missing.Value);
                    string excelFileName = oNaam as string;

                    if (excelFileName == null || excelFileName.Trim().Equals(""))
                    {
                        log.Debug("End reading responses end with row " + teller.ToString());
                        break;
                    }

                    log.Debug("Run name " + excelFileName);

                    oRowSource.Copy(oRowTarget);

                    ((Excel.Worksheet)excelAppTemplate.ActiveWorkbook.Sheets[1]).Select(Type.Missing); //activates first sheet

                    Worksheet wsTemplate = getWorksheetByName("Template");

                    // Auto fit
                    for (int r = 115; r < 121; r++)
                        wsTemplate.get_Range("A" + r.ToString(), "A" + r.ToString()).EntireRow.AutoFit();

                    for (int r = 144; r < 155; r++)
                        wsTemplate.get_Range("A" + r.ToString(), "A" + r.ToString()).EntireRow.AutoFit();

                    // Run Macros
                    RunMacro(excelAppTemplate, new Object[] { "Blad1.RunMacros" });

                    string filePathXls = REPORT_DIR_EXCEL + excelFileName.Trim() + ".xls";
                    string filePathPdf = REPORT_DIR_PDF + excelFileName.Trim() + ".pdf";

                    // Delete XLS is exists
                    if (File.Exists(filePathXls))
                        File.Delete(filePathXls);

                    HideNonTemplateSheets();

                    // Save XLS
                    if (saveExcel) {
                        wbTemplate.SaveAs(filePathXls);
                        log.Debug("Saved " + filePathXls);
                    }

                    // Delete PDF is exists
                    if (File.Exists(filePathPdf))
                        File.Delete(filePathPdf);

                    if (savePdf) {
                        wbTemplate.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, filePathPdf, XlFixedFormatQuality.xlQualityStandard, paramIncludeDocProps,
                                paramIgnorePrintAreas, paramFromPage, paramToPage, paramOpenAfterPublish, Type.Missing);
                        log.Debug("Saved " + filePathPdf);
                    }

                    teller++;
                }
            }
            catch (Exception e)
            {
                log.Error("Failed to generate excel file.", e);
            }
            finally
            {
                Close();
            }
        }

        private void HideNonTemplateSheets()
        {
            // Hide sheets
            int sheetNum = 2;
            int sheetCount = excelAppTemplate.ActiveWorkbook.Sheets.Count;
            while (sheetNum <= sheetCount)
            {
                ((Excel.Worksheet)excelAppTemplate.ActiveWorkbook.Sheets[sheetNum]).Visible = Excel.XlSheetVisibility.xlSheetHidden;
                sheetNum++;
            }
        }

        private void OpenExcelTemplate()
        {
            excelAppTemplate = new Excel.Application();
            object paramMissing = Type.Missing;

            // Open the source workbook.
            wbTemplate = excelAppTemplate.Workbooks.Open(TEMPLATE_FILE_EXCEL, paramMissing, paramMissing, paramMissing,
                paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing,
                paramMissing, paramMissing, paramMissing, paramMissing, paramMissing);
        }

        private Worksheet getWorksheetByName(string name)
        {
            foreach (Worksheet sheet in wbTemplate.Worksheets)
            {
                if (sheet.Name == name)
                {
                    return sheet;
                }
            }
            return null;
        }

        private void Close()
        {
            // Close the workbook object.
            if (wbTemplate != null)
            {
                wbTemplate.Close(false);
                wbTemplate = null;
            }

            // Quit Excel and release the ApplicationClass object.
            if (excelAppTemplate != null)
            {
                excelAppTemplate.Quit();
                excelAppTemplate = null;
            }

            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

        private void RunMacro(object oApp, object[] oRunArgs)
        {
            lock (this)
            {
                oApp.GetType().InvokeMember("Run",
                 System.Reflection.BindingFlags.Default |
                 System.Reflection.BindingFlags.InvokeMethod,
                 null, oApp, oRunArgs);
            }
        }
    }
}
